<?php

class SubjectTagTable extends GenericObjectTypeTable {

    public function getTagsByNamesQuery(array $tagNames, $q = null) {
        if ($q == null) {
            $q = $this->createQuery();
        }

        $q->WhereIn('UPPER(typeName)', $tagNames);

        return $q;
    }

    public function getTagsByPartNameQuery($tagPartName, $q = null) {
        if ($q == null) {
            $q = $this->createQuery();
        }

        $q->addWhere('UPPER(typeName) like ?', strtoupper($tagPartName) . '%');
        return $q;
    }

    /**
     * Updates the frequencies of the Tags to display a relative percentage of 
     * their popularity
     */
    public function updateTagFrequencies() {
        $q = Doctrine_Query::create();
        $q->update('SubjectTag');
        $q->set('frequency', 'trunc(((select cast(count(*) as float) from Collection_Tag where Collection_Tag.subjectTagUin = Subject_Tag.uin)/(select count(*) from Collection_tag))*100)');
        $q->execute();
    }

    public function addOrderByFrequencyQueryDesc($q = null, $includeUnused = false) {
        if ($q == null) {
            $q = $this->createQuery();
        }

        $q->addOrderBy('frequency desc');

        if (!$includeUnused) {
            $q->addWhere('frequency > 0');
        }
        return $q;
    }

    public function getRelatedSubjectTagsQuery($subjectTags) {

        foreach ($subjectTags as $index => $subjectTag) {
            $subjectTags[$index] = strtoupper($subjectTag);
        }

        /*
         * This is problematic - it appears to have a problem in that it
         * returns all the values for both SubjectTag and COllectionTAg
         * thus the distinct clause does nto work propertly
         * and returns tags that will not result in any collections
         */
        $q  = $this->createQuery('si');
        $q->select('si.typeName');
        $q->distinct();
        $q->innerJoin('si.CollectionTag cti');
        foreach ($subjectTags as $index => $subjectTag) {
           $q->andWhere('cti.collectionuin IN (SELECT ct'.$index.'.collectionuin FROM SubjectTag s'.$index.' '.
                    'INNER JOIN s'.$index.'.CollectionTag ct'.$index.' '.
                    'WHERE upper(s'.$index.'.typename) = ?)', $subjectTag);
        }
        $q->whereNotIn('UPPER(si.typeName)',$subjectTags);
        //echo $q->getSqlQuery();
        return $q;
    }

    public static function getInstance() {
        return Doctrine_Core::getTable('SubjectTag');
    }

}